from flask import Blueprint, request, jsonify, send_from_directory
from pathlib import Path
import uuid
import json
import os
from datetime import datetime
from db.sqlite import SQLiteDB
from utils.jobupload import upload


job_router = Blueprint('job', __name__, url_prefix='/job')
@job_router.route('/add', methods=['POST'])
def add():
    posts = request.get_json()
    files = posts.get('files',[])
    file = files[0]
    accounts = posts.get('accounts',[])
    topics = posts.get('topics',[])
    title = posts.get('title','')
    publishTime = posts.get('publishTime','')
    if not file:
        return jsonify({"code": 400,"msg": "file is empty", "data": None})
    if not accounts:
        return jsonify({"code": 400,"msg": "accounts is empty", "data": None})
    if not topics:
        return jsonify({"code": 400,"msg": "topics is empty", "data": None})
    if not title:
        return jsonify({"code": 400,"msg": "title is empty", "data": None})
    if not publishTime:
        return jsonify({"code": 400,"msg": "publishTime is empty", "data": None})
    # 迭代topics 二维数组 得到name
    topic_list = [topic['name'] for topic in topics]

    # 将publishTime 转为标准时间
    # publishTime = datetime.strptime(publishTime, "%Y-%m-%d %H:%M:%S")
    publishTime =  datetime.strptime(publishTime, "%Y-%m-%dT%H:%M:%S.%fZ")
    print(publishTime)

    # 准备数据
    list = []
    for account in accounts:
        info = {
            "account": account,
            "file": file,
            "title": title,
            "topic": topic_list,
            "publish": publishTime,
        }
        list.append(info)

    # 循环插入
    total = 0
    try:
        with SQLiteDB() as db:
            for info in list:
                insert_sql = "INSERT INTO job (account, file, title, topic, publish,status) VALUES (?, ?, ?, ?, ?, ?)"
                affected_rows = db.execute(insert_sql, (json.dumps(info['account']), json.dumps(info['file']), info['title'], json.dumps(info['topic']), info['publish'],1))
                total += affected_rows
                db.commit()
                print(f"插入成功，受影响行数: {affected_rows}")
        return jsonify({"code": 200,"msg": "ok", "data": {"total": total}})
    except Exception as e:
        return jsonify({"code": 400,"msg": e,"data": None})

@job_router.route('/delete', methods=['GET'])
def delete():
    job_id = request.args.get('id')
    if not job_id or not job_id.isdigit():
        return jsonify({"code": 400,"msg": "Invalid or missing job ID", "data": None})
    try:
        # 获取数据库连接
        with SQLiteDB() as db:
            # 查询要删除的记录
            results = db.query("SELECT * FROM job WHERE id = ?", (job_id,))
            if not results:
                return jsonify({"code": 404, "msg": "job not found", "data": None})
            job = results[0]
            if job['status'] == 5:
                return jsonify({"code": 400, "msg": "job is runing", "data": None})
            # 删除数据库记录
            db.execute("DELETE FROM job WHERE id = ?", (job_id,))
            db.commit()
        return jsonify({"code": 200,"msg": "ok", "data": { "id": job_id }})
    except Exception as e:
        return jsonify({ "code": 500,"msg": str(e), "data": None})

@job_router.route('/cancel', methods=['GET'])
def cancel():
    job_id = request.args.get('id')
    if not job_id or not job_id.isdigit():
        return jsonify({"code": 400,"msg": "Invalid or missing job ID", "data": None})
    try:
        # 获取数据库连接
        with SQLiteDB() as db:
            # 查询要删除的记录
            results = db.query("SELECT * FROM job WHERE id = ?", (job_id,))
            if not results:
                return jsonify({"code": 404, "msg": "job not found", "data": None})
            job = results[0]
            if job['status'] > 1:
                return jsonify({"code": 400, "msg": "job has already been completed", "data": None})
            # 删除数据库记录
            db.execute("UPDATE job SET status = ? WHERE id = ?", (9,job_id,))
            db.commit()
        return jsonify({"code": 200,"msg": "ok", "data": { "id": job_id }})
    except Exception as e:
        return jsonify({ "code": 500,"msg": str(e), "data": None})
@job_router.route('/list', methods=['GET'])
def list():
    page = request.args.get('page')
    limit = request.args.get('limit')
    if not page or not page.isdigit():
        return jsonify({"code": 400,"msg": "Invalid or missing page", "data": None})
    if not limit or not limit.isdigit():
        return jsonify({"code": 400,"msg": "Invalid or missing limit", "data": None})
    offset = (int(page) - 1) * int(limit)
    if offset < 0:
        offset = 0
    try:
        with SQLiteDB() as db:
            # 查询所有记录
            list = db.query("SELECT * FROM job ORDER BY id DESC LIMIT ?,?",(offset,int(limit)))
            # 遍历list  还原字段json 类型
            for item in list:
                item['account'] = json.loads(item['account'])
                item['file'] = json.loads(item['file'])
                item['topic'] = json.loads(item['topic'],)
            info = db.query("SELECT COUNT(*) AS total FROM job")
            total = info[0]['total']


        return jsonify({ "code": 200,"msg": "success", "data": {"list": list, "total": total}})
    except Exception as e:
        return jsonify({"code": 500, "msg": str("get file failed!"),"data": None })

@job_router.route('/toplist', methods=['GET'])
def toplist():
    try:
        with SQLiteDB() as db:
            # 查询所有记录
            list = db.query("SELECT * FROM job WHERE status = 1 ORDER BY id DESC LIMIT ?,?",(0,5))
            # 遍历list  还原字段json 类型
            for item in list:
                item['account'] = json.loads(item['account'])
                item['file'] = json.loads(item['file'])
                item['topic'] = json.loads(item['topic'],)
            info = db.query("SELECT COUNT(*) AS total FROM job")
            total = info[0]['total']
        return jsonify({ "code": 200,"msg": "success", "data": {"list": list, "total": total}})
    except Exception as e:
        return jsonify({"code": 500, "msg": str("get file failed!"),"data": None })


@job_router.route('/exclute', methods=['GET'])
def exclute():
    job_id = request.args.get('id')
    if not job_id or not job_id.isdigit():
        return jsonify({"code": 400,"msg": "Invalid or missing job ID", "data": None})
    try:
        # 获取数据库连接
        with SQLiteDB() as db:
            # 查询要删除的记录
            results = db.query("SELECT * FROM job WHERE id = ?", (job_id,))
            if not results:
                return jsonify({"code": 404, "msg": "job not found", "data": None})
            job = results[0]
            # 调用助手执行
            if job['status'] > 1:
                return jsonify({"code": 400, "msg": "job has already been completed", "data": None})
           
            db.execute("UPDATE job SET status = ? WHERE id = ?", (5,job_id,))
            db.commit()
            upload(job_id)
            db.execute("UPDATE job SET status = ? WHERE id = ?", (10,job_id,))
            db.commit()
        return jsonify({"code": 200,"msg": "ok", "data": { "id": job_id }})
    except Exception as e:
        return jsonify({ "code": 500,"msg": str(e), "data": None})